﻿Imports System.Runtime.InteropServices      'COMException 사용하기 위해서.
Imports System.IO



Public Class Oracle

    Private Shared instance As New Oracle()
    Private Shared instance2 As New Oracle()
    Private Shared instance3 As New Oracle()
    Private m_oraSession As OracleInProcServer.OraSessionClass
    Private m_oraDatabase As OracleInProcServer.OraDatabase
    Private m_oraSession2 As OracleInProcServer.OraSessionClass
    Private m_oraDatabase2 As OracleInProcServer.OraDatabase
    Private m_oraParamArray() As OracleInProcServer.OraParamArray



    Private Sub New()

    End Sub



    ''' <summary>
    ''' ClsOracle 클래스의 인스턴스를 반환
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetInstance() As Oracle
        Return instance
    End Function



    Public Shared Function GetInstance2() As Oracle
        Return instance2
    End Function



    Public Shared Function GetInstance3() As Oracle
        Return instance3
    End Function



    



    ''' <summary>
    ''' 오라클 데이터베이스에 연결한다.
    ''' </summary>
    ''' <param name="val_strConn">연결문자열</param>
    ''' <param name="val_strUserName">오라클 유저명</param>
    ''' <param name="val_strUserPwd">오라클 유저 패스워드</param>
    ''' <remarks></remarks>
    Public Sub Connect(ByVal val_strConn As String, ByVal val_strUserName As String, ByVal val_strUserPwd As String)
        Try
            m_oraSession = New OracleInProcServer.OraSessionClass
            m_oraDatabase = m_oraSession.OpenDatabase(val_strConn, val_strUserName & "/" & val_strUserPwd, 0)
            m_oraDatabase.AutoCommit = True

            'm_oraSession2   = new OracleInProcServer.OraSessionClass 
            'm_oraDatabase2  = m_oraSession.OpenDatabase(val_strConn, val_strUserName & "/" & val_strUserPwd, 0)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            Application.Exit()
        End Try

        'm_oraDatabase.AutoCommit = false
    End Sub



    ''' <summary>
    ''' SELECT ~~ 쿼리문을 실행하고 그 결과값을 반환한다.
    ''' </summary>
    ''' <param name="v_strSQL">실행할 SQL</param>
    ''' <returns></returns>
    Public Function CreateDynaset(ByVal v_strSQL As String, Optional ByVal v_iOption As Integer = OracleInProcServer.dynOption.ORADYN_DEFAULT) As OracleInProcServer.OraDynaset
        Try

            Return m_oraDatabase.CreateDynaset(v_strSQL, v_iOption)
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Function



    ''' <summary>
    ''' (POOL) SELECT ~ 쿼리문을 실행하고 그 결과값을 반환한다.
    ''' </summary>
    ''' <param name="v_strSQL"></param>
    ''' <param name="v_oraDatabase"></param>
    ''' <param name="v_iOption"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateDynaset(ByVal v_strSQL As String, ByRef v_oraDatabase As OracleInProcServer.OraDatabase, Optional ByVal v_iOption As Integer = OracleInProcServer.dynOption.ORADYN_DEFAULT) As OracleInProcServer.OraDynaset
        Try
            Return v_oraDatabase.CreateDynaset(v_strSQL, v_iOption)
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Function



    ''' <summary>
    ''' 리턴값(레코드셋)이 없는 SQL 문을 실행한다.
    ''' </summary>
    ''' <param name="v_strSQL">실행할 SQL</param>
    Public Function ExecSQL(ByVal v_strSQL As String) As Integer
        Try
            Return m_oraDatabase.ExecuteSQL(v_strSQL)
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try


    End Function



    ''' <summary>
    ''' 트랜잭션을 시작함
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTrans()
        Try
            m_oraSession.ResetTrans()
            m_oraSession.BeginTrans()
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Sub



    ''' <summary>
    ''' 현재 활성화된 트랜잭션을 롤백함
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Rollback()
        Try
            m_oraSession.Rollback()
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Sub



    ''' <summary>
    ''' 현재 활성화된 트랜잭션을 커밋함
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Commit()
        Try
            m_oraSession.CommitTrans()
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Sub




    ''' <summary>
    ''' DB 연결을 끊는다.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Disconnect()
        Try
            If Not (m_oraSession Is Nothing) Then
                m_oraDatabase.Close()
                m_oraSession.ResetTrans()
            End If
        Catch comEx As COMException
            Throw comEx
        Catch ex As Exception
            Throw ex
        End Try
    End Sub



    Public Function IsConnected() As Boolean
        If m_oraSession Is Nothing Then
            IsConnected = False
        Else
            IsConnected = True
        End If
    End Function



End Class


